
-- columna id para tabla valtxcaj. Es la nueva pk
-- ale 20/10/2020


CREATE SEQUENCE public.seq_valtxcaj
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.seq_valtxcaj  OWNER TO abacouser;

-- valtxcaj.id como PK
ALTER TABLE valtxcaj DROP CONSTRAINT valtxcaj_pkey;

ALTER TABLE valtxcaj ADD COLUMN id integer NOT NULL DEFAULT nextval('public.seq_valtxcaj');
ALTER TABLE valtxcaj ADD CONSTRAINT valtxcaj_pkey PRIMARY KEY (id);                      

ALTER TABLE valtxcaj ADD COLUMN idambdb INTEGER, 
                     ADD COLUMN fechainsert timestamp without time zone, 
                     ADD COLUMN fechaupdate timestamp without time zone;

-- indice UNIQUE (idpad-valt)
CREATE UNIQUE INDEX valtxcaj_idpad_tipovalt
  ON public.valtxcaj
  USING btree (idpad, tipovalt COLLATE pg_catalog."default");

-- foreign keys
ALTER TABLE valtxcaj 
  ADD CONSTRAINT gestion_ai_valtxcajpad FOREIGN KEY (idpad) REFERENCES pad (id)                      
  ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE valtxcaj 
  ADD CONSTRAINT gestion_ai_valtxcajvalt FOREIGN KEY (tipovalt) REFERENCES valt (tipo)                      
  ON UPDATE CASCADE ON DELETE CASCADE;

-- comments
COMMENT ON TABLE public.valtxcaj IS 'Tipos de valor para caja extendida - (Tipos de valor a incluir en la caja sin ser de caja o siendo de otras cajas)';
COMMENT ON COLUMN public.valtxcaj.idpad IS 'Id de la caja';
COMMENT ON COLUMN public.valtxcaj.idpad IS 'Tipo de valor a incluir';


-- ====== Trigger auditoria =====
CREATE TRIGGER control_audit_valtxcaj
  BEFORE INSERT OR UPDATE OR DELETE
  ON public.valtxcaj
  FOR EACH ROW
  EXECUTE PROCEDURE public.tg_control_audit_instancia();

-- =====================================

-- actualizar la secuencia (x si habia filas en la tabla)
SELECT public.seq_actualizarsecuencias();

-- ==================================
SELECT aaVersion('442.01','442.00');
-- ==================================